Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

Hello again! Today you're going to finish the main part of the back-end application for your storefront Web site. Now that you've got a few categories and products pushed into the database, you'll want the store manager to be able to view the products and be able to update information in them (such as change the price or quantity values).

You'll use some tricks to display the product image on the Web site. First, you'll look at how to extract the product image BLOB object from the table and display it. Then you'll create a special program to help with that process.

After displaying the store products, you need to provide an interface for the store manager to modify or even delete products. You've worked with inserting data into a table, but we haven't discussed how to update an existing table record. We'll start by looking at how to populate an HTML form with existing database data.

Finally, you'll learn what you need to do to update or remove data from a table. There are a few things to watch out for when doing this (you don't want to accidentally change the price on all the products or, even worse, remove all of your records); so we'll take our time going over this process.

If you're ready, let's get started.

Displaying Images

So far in your project, you've been able to write the code that allows the store manager to create categories and products, including providing fancy pictures for each product. Now it's time to figure out how you can display that information for the store manager to see.

Extracting and displaying images is somewhat complicated in PHP. You have to go from a BLOB database object to a normal image displaying on your Web page. You need to perform a couple of steps to get from one to the other.

In the normal HTML world, you display images on a Web page using the <img> tag. This tag defines a source location for the image and often a size for the browser to use to display the image.

<img src="myimage.jpg" width="80" height="60">

That's fine if your image is stored on the server hard drive, but your images are stored as BLOB objects in the database table. That makes things somewhat tricky. You can't just point the src attribute to your database object.

Instead, you need to create a small PHP program that'll display your image, and then point the <img> tag to that program. Since this is a PHP program, you can spice things up a bit and make it dynamic. You'll use the HTML GET method to pass along the product ID number of the image you want to see, then use that in your program to display the proper image. Your <img> tag will look like this:

<img src="showimage.php?id=1" width="80" height="60">

This will show the image for the product with productid equal to one. When we get into the programming, you'll use PHP to generate this value as well.

Now you need to create the showimage.php program that'll display your image. Follow these steps to do that:

1. Create a file called showimage.php in the admin folder under the store area.
2. Use a text editor to add the following code to the file:

<?php
   header("Content-type: image/jpeg");
   $prodid = $_GET['id'];
   $con = mysql_connect("localhost", "test", "test") or die('');
   mysql_select_db("store", $con);
   $query = "SELECT picture from products WHERE prodid = $prodid";
   $result = mysql_query($query);
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $picture = $row['picture'];
   echo $picture;
?>

3. Save the file and exit the editor.
4. Copy the showimage.php code file to the store folder so it appears in both the admin and store folders.

That's all there is to it! Two keys make this code work. The most important piece is the header() function:

header("Content-type: image/jpeg");

This function sends an HTTP header to your browser. The Content-type: header indicates that the data following the header is a JPEG image. When your browser sees this, it knows to display it as an image and not as normal data. Without it, your browser would just display the binary data of your image.

After sending the HTTP header, you need to send the raw image data. That's just a matter of retrieving the correct record from the table and using the echo statement to send it to the browser. The echo statement is capable of sending the binary data contained in the BLOB object to the browser.

Now all you need to do is make a program that uses this technique to display the image along with the product information.

Testing It Out

Before you get too deep in your code, it's always a good idea to test out routines in a simpler environment. This helps prevent the "looking for a needle in a haystack" syndrome you get trying to debug features in a program that has hundreds of modules.

To test out the image display, we'll just create a simple Web page that shows all of the images stored in the database. Once you get this working, you can move on to creating the official Web page that the store manager will use to edit product data. First, let's create our test program:

1. Create a file called imagetest.php in the store folder.
2. Open the file with a text editor, and enter the following code:


Print code


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>Image Test</title>
<body>
<?php
include("mylibrary/login.php");
login();
$query = "SELECT prodid, description FROM products";
$result = mysql_query($query) or die(mysql_error());
echo "<table width=\"50%\" cellpadding=\"1\" border=\"1\">\n";
echo "<tr><td>Product ID</td><td>Description</td><td>Image</td></tr>\n";
while($row=mysql_fetch_array($result, MYSQL_ASSOC))
{
   $prodid = $row['prodid'];
   $description = $row['description'];
   echo "<tr><td>$prodid</td><td>$description</td>\n";
   echo "<td><img src=\"showimage.php?id=$prodid\" width=\"80\" height=\"60\"></td></tr>\n";
}
echo "</table>\n";
?>
</body>
</html>

This simple test program shows how you can use an HTML table in your output to help organize the data you present to your Web site visitors. Notice the <img> tag uses the new showimage.php file to display the image and passes the product ID value for the current product. To view your creation, just use the URL:

http://localhost/store/imagetest.php.

You should see something like this:



Viewing images stored in the database

Now you're ready to build the code to present the product data to your store manager. Continue on to Chapter 3 to see how that's done.

Showing Images With Product Information

One of the options in the adminnav.inc.php file produces a select form area using the product categories as entries. This allows the manager to select a category for editing product information. It helps limit the products you display on the Web page. Now it's time to use that information to display your products.

If you remember, the adminnav.inc.php select form action attribute points to the admin.php main Web page with the content variable set to editproducts. This defines the page you need to create to display your product information.

The editproducts.inc.php file is responsible for displaying all of the products for a certain category. Let's create that file, then look at how it works:

1. Create a file called editproducts.inc.php in the admin folder under the store application area.
2. Use a text editor to add the following code to the file:

Print code

 <?php
   if (!isset($_SESSION['store_admin']))
   {
      echo "<h2>Sorry, you have not logged into the system</h2>\n";
      echo "<a href=\"admin.php\">Please login</a>\n";
   } else
   {
      echo "<h2>Click on a product to edit it</h2>\n";
      $catid = $_GET['cat'];
      if (!isset($_GET['page']))
         $page = 1;
      else
         $page = $_GET['page'];
      showproducts($catid, $page, "admin.php?content=editproducts", "admin.php?content=updateproduct");
   }
?>

That's not very much code, considering what the program's supposed to be doing! It first checks to make sure the Web page visitor is logged in, and then it retrieves two HTML variables: cat and page. It passes both of these variables to a new function, called showproduts(), along with a couple of URLs. The showproducts() function uses four parameters:

• The category ID from which to display products.
• The page of products to display.
• The URL of the current Web page.
• A URL for the Web page to go to if the visitor clicks on a product.

The Food Store has two situations where you'll need to display products. The store manager will need to list products to edit them, and the store customers will need to browse through products to select them for purchasing. By making the showproducts function as generic as possible, you can use it for both the store back-end and front-end applications (that's the whole point of creating a function).

The page value allows you to display only a subset of the total product list. This makes life a little easier for the viewer trying to browse the catalog. Instead of showing all of the products, the visitor will see a group of five products and links to go to the next or previous pages.

Now you need to create the showproducts.php function file to handle displaying product data:

1. Create a file called showproducts.php in the mylibrary folder under the store folder.
2. Open the file with a text editor and enter the following code:


Print code


<?php
   function showproducts($catid, $page, $currentpage, $newpage)
   {
      $query = "Select count(prodid) from products where catid = $catid";
      $result = mysql_query($query);
      $row = mysql_fetch_array($result);
      if ($row[0] == 0)
      {
         echo "<h2><br>Sorry, there are no products in this category</h2>\n";
      }
      else
      {
         $thispage = $page;
         $totrecords = $row[0];
         $recordsperpage = 5;
         $offset = ($thispage - 1) * $recordsperpage;
         $totpages = ceil($totrecords / $recordsperpage);
         echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
         echo "<tr><td><h2>Image</h2></td>\n";
         echo "<td><h2>Product</h2></td>\n";
         echo "<td><h2>Price</h2></td>\n";
         echo "<td><h2>Quantity in Stock</h2></td>\n";
         echo "<td><h2>Special</h2></td></tr>\n";
         $query = "SELECT * from products WHERE catid=$catid LIMIT $offset,$recordsperpage";
         $result = mysql_query($query);
         while($row=mysql_fetch_array($result, MYSQL_ASSOC))
         {
            $prodid = $row['prodid'];
            $description = $row['description'];
            $price = $row['price'];
            $quantity = $row['quantity'];
            $onsale = $row['onsale'];
 
            echo "<tr><td>\n";
               echo "<img src=\"showimage.php?id=$prodid\" width=\"80\" height=\"60\">";
            echo "</td><td>\n";
               echo "<a href=\"$newpage&id=$prodid\">$description\n";
            echo "</td><td>\n";
               echo "$" . $price . "\n";
            echo "</td><td>\n";
               echo $quantity . "\n";
            echo "</td><td>\n";
            if ($onsale)
               echo "On sale!\n";
            else
               echo "&nbsp;\n";
            echo "</td></tr>\n";
         }
         echo "</table>\n";
   // Code to implement paging
         if ($thispage > 1)
         {
            $page = $thispage - 1;
            $prevpage = "<a href=\"$currentpage&cat=$catid&page=$page\">Previous page</a>";
         } else
         {
            $prevpage = " ";
         }
         if ($thispage < $totpages)
         {
            $page = $thispage + 1;
            $nextpage = " <a href=\"$currentpage&cat=$catid&page=$page\">Next page</a>";
         } else
         {
            $nextpage = " ";
         }
         if ($totpages > 1)
            echo $prevpage . "  " . $nextpage;
      }
   }
?>

3. Save the file and exit the editor.
4. Open the admin.php file in an editor, and add the showproducts.php file as an include file:

<?php
   include("/mylibrary/login.php");
   include("/mylibrary/getThumb.php");
   include("/mylibrary/showproducts.php");
   login();
?>

5. Save the file and exit the editor

You should recognize most of this code. It uses the HTML table technique you tested in the imgetest.php program to display the product image (using the <img> tag), the description, price, quantity in stock, and if the product is on sale. There are two things happening here:

• The product description is a link that passes the product ID value to the new page specified in the calling program.
• The onsale value is stored in the database as a Boolean value (true or false), so you need to use an if-then statement to display more descriptive text if the value is set.

The onsale code looks like this:

if ($onsale)
   echo "On sale!\n";
else
   echo " \n";

If the Boolean value is set, it displays the On sale! text. If not, you still need to put something in the table cell, or the browser won't paint the cell correctly. You use the HTML space character, &nbsp;, to enter a blank space.

That should do it. Test this out by selecting a category in the navigation area drop-down box, then click the Browse button. You should see the products in your database appear on the Web page!

Displaying products in a category

Now all that's left to do is create a form for the store manager to update products. We'll do that in Chapter 4.

Updating and Deleting Records

So far, you've seen how to insert new records into a database using HTML forms. If you want to allow your store manager to update existing products, it's a little different.

Not only do you have to create the HTML form, but you must be able to populate the form with the existing data in the database. When the store manager is done making changes, you must provide a button that allows him or her to send the changes back to the database.

If you remember from earlier, the HTML form used to input data into the products table uses the text input type. To set a default value for a text input type, all you need is to define it using the value attribute:

echo "<input type=\"text\" name=\"price\" value=\"$price\">\n";

When the form appears, the browser shows the value data inside the textbox. If the store manager clicks the button to send the data, the default data is sent back as part of the form data.

Notice that even though the price data field is a numerical value, the HTML form still treats it as a text value. The value the form returns is also a text value, which works fine, since you'll use the text version of the price to create the SQL string needed to update the value in the table.

The editproducts.inc.php program passed the URL admin.php?content=updateproduct to the showproducts() function. When the showproducts() function lists the products, it creates a link for the product description that points to the updateproduct.inc.php Web page. This page paints the HTML form for our store manager.

Let's create this code now.

1. Create a file called updateproduct.inc.php in the admin folder in the store folder.
2. Open the file in a text editor, and enter the following code:

Print code


<?php
   $prodid = $_GET['id'];
   $query = "SELECT prodid, catid, description, price, quantity, onsale FROM products where prodid = $prodid";
   $result = mysql_query($query);
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $catid = $row['catid'];
   $description = $row['description'];
   $price = $row['price'];
   $quantity = $row['quantity'];
   $onsale = $row['onsale'];
   $query = "SELECT name FROM categories WHERE catid = $catid";
   $result=mysql_query($query);
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $catname = $row['name'];
   echo "<h2>Update Product Information</h2>\n";
   echo "<form enctype=\"multipart/form-data\" action=\"admin.php\" method=\"post\">\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"changeproduct\">\n";
   echo "<input type=\"hidden\" name=\"prodid\" value=\"$prodid\">\n";
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td><h3>Product ID</h3></td><td>$prodid</td></tr>\n";
   echo "<tr><td><h3>Category</h3></td>\n";
   echo "<td><select name=\"catid\">\n";
   $query="SELECT catid,name from categories";
   $result=mysql_query($query);
   while($row=mysql_fetch_array($result,MYSQL_ASSOC))
   {
       $tempcatid = $row['catid'];
       $name = $row['name'];
       if ($tempcatid == $catid)
         echo "<option value=\"$tempcatid\" selected=\"selected\">$name</option>\n";
       else
         echo "<option value=\"$tempcatid\">$name</option>";
   }
   echo "</select></td></tr>\n";
   echo "<tr><td><h3>Description</h3></td><td><input type=\"text\" name=\"description\" value=\"$description\"></td></tr>\n";
   echo "<tr><td><h3>Price</h3></td><td><input type=\"text\" name=\"price\" value=\"$price\"></td></tr>\n";
   echo "<tr><td><h3>Quantity</h3></td><td><input type=\"text\" name=\"quantity\" value=\"$quantity\"></td></tr>\n";
   if ($onsale)
      echo "<tr><td><h3>On Sale</h3></td><td><input type=\"checkbox\" name=\"onsale\" value=\"1\" checked></td></tr>\n";
   else
      echo "<tr><td><h3>On Sale</h3></td><td><input type=\"checkbox\" name=\"onsale\" value=\"1\"></td></tr>\n";
   echo "<tr><td><h3>Image</h3></td><td><img src=\"showimage.php?id=$prodid\" width=\"80\" height=\"60\"></td></tr>\n";
   echo "<tr><td><h3>Update image</h3></td><td><input type=\"file\" name=\"picture\"></td></tr>\n";
   echo "</table>\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Update\">\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Delete Product\">\n";
   echo "</form>\n";
?>

That's a lot of code! You should recognize most of it, though. The first thing the code does is extract the product ID value passed by the link. It then queries the products table to retrieve the existing information for the product.

The code uses that information to populate the HTML form, one entry for each data field. There are a couple of cool things to see here:

• It uses a select input type form to select a new category for the product. When it creates the drop-down box, it marks the current data value for the catid field as selected. That makes it the default value in the drop-down box.
• The onsale data field is created as a check box. If the onsale value is true, the check box is marked as checked. If the value is false, the check box isn't checked. This is a great way to indicate Boolean data type values on a form. The Web site visitor can easily see if the value is set or not.
• The current image is displayed in the form, and another field to enter a new image name is added. If the manager leaves this entry blank, it'll keep the existing image.
• There are two submit buttons for the form. One indicates we want to update the information, and the other indicates we want to remove the product from the database.

Okay, now that the form is there, we need something to retrieve the data from the form and update the database information accordingly. That's the job of the changeproduct.inc.php file:

1. Create a file called changeproduct.inc.php in the admin folder, under the store area.
2. Enter the following code into the file:


Print code


<?php
   $delete = $_POST['button'];
   if ($delete == "Delete Product")
   {
      $prodid = $_POST['prodid'];
      $query = "DELETE from products WHERE prodid = $prodid";
      $result = mysql_query($query);
      if ($result)
      {
         echo "<h2>Product: $prodid deleted</h2>\n";
         exit;
      } else
      {
         echo "<h2>Problem deleting $prodid</h2>\n";
         exit;
      }
   } else
   {
      $prodid = $_POST['prodid'];
      $catid = $_POST['catid'];
      $description = $_POST['description'];
      $price = $_POST['price'];
      $quantity = $_POST['quantity'];
      if (get_magic_quotes_gpc())
      {
         $description = stripslashes($desription);
      }
      $description = mysql_real_escape_string($description);
      if (isset($_POST['onsale']))
         $onsale = 1;
      else
         $onsale = 0;
      $PictName = $_FILES['picture']['name'];
      if ($PictName)
      {
         $thumbnail = getThumb($_FILES['picture']);
         $thumbnail = mysql_real_escape_string($thumbnail);
         $query = "UPDATE products SET catid='$catid', description = '$description', " .
                 "price = $price, quantity = $quantity, onsale = $onsale, picture = '$thumbnail' " .
                 "WHERE prodid = $prodid";
      }
      else
      {
         $query = "UPDATE products SET catid='$catid', description = '$description', " .
                 "price = $price, quantity = $quantity, onsale = $onsale " .
                 "WHERE prodid = $prodid";
      }
      $result = mysql_query($query) or die(mysql_error());
      if ($result)
      {
         echo "<h2>Product information changed.</h2>\n";
      }
      else
      {
         echo "<h2>Sorry, I could not change the product information.</h2>\n";
      }
   }
?>

The first thing the code needs to detect is which submit button the manager selected in the form. In the form, you assigned both buttons the same name but gave them different values. All you need to do is retrieve the data for the button name and determine which value appears.

If the manager selects the button to remove the record, the code creates a DELETE SQL statement and submits it to the server. You must be careful when working with DELETE statements. By default, a DELETE statement that has no WHERE clause will delete every record in your database! That little tidbit has bitten quite a few database programmers. The WHERE clause specifies which records you want deleted. In your case, you must specify the product ID you want to delete.

If the manager selects the Update button, you must retrieve all of the posted data from the HTML form. Again, there are a couple of interesting things happening here as well:

• The onsale check box is only set if there's a check in it. Use the isset() function to determine if that variable is set or not. If so, assign a true value (1) to the variable.
• If a new image is set in the image field, the $_FILES[] value will be set. You assign the ['name'] element to a variable and check if it exists. If it does exist, you need to create a new thumbnail using your friend getThumb(), then use that value along with the other data values to update the record. If no new image file was specified, you can leave the picture BLOB alone and just replace the other data.

The UPDATE SQL statement updates data in records. Using the set clause, you can set individual data field values:

Print code


UPDATE products SET catid=1, description = 'test', price =1.25, quantity =10, onsale = 1          WHERE prodid = 1;

Again, just like the DELETE statement, the WHERE clause is important. It defines which records get the updated data. Without it, the UPDATE statement updates all of the records with the new data, which would not be a very good thing.

The UPDATE statement can also use equations to update the data fields:

UPDATE products SET quantity = quantity - 5 WHERE prodid = 10;

This statement reduces the quantity data field by five for the product with prodid of 10.

Once you have your code created, give it a test run. You should be able to update any data field in the products table using the HTML form, including specifying a new image for the product.



Updating product information

That's quite enough for one day! Let's go on to Chapter 5 and finish things up.

Summary

Displaying images stored in the MySQL database requires a few different steps. To display the image on a Web page, you must use the HTML <img> tag. The <img> tag points to a local file or remote URL to retrieve the image. Since the image is stored in the database, you must create a small PHP code snippet that retrieves the correct image from the database to display. The code must first send the HTTP Content-Type header field to the browser, then send the raw image data. The PHP header() function sends the HTTP Content-Type header, and you can just use the standard echo statement to send the image data stored in the database.

Allowing the store manager to update product information is another important topic. You can create an HTML form using the product information stored in the database by retrieving the data and by using the HTML text input type value attribute. This makes the existing data the default value in the form. If the manager doesn't modify the field, the default value is passed to the PHP code to update the database field.

When you retrieve the updated data, you'll need to push it back into the database. The UPDATE SQL statement allows you to update data in existing records. The UPDATE statement requires a WHERE clause to limit which records receive the new data. By default, all records will receive the data, which is not what you want!

Finally, we discussed the DELETE SQL statement. If the manager wants to remove a product from the database, the code builds a DELETE statement, again using the WHERE clause to specify exactly which record should be deleted.

In the next lesson, we'll turn our attention to the front end of the Food Store application. We'll see what elements you need to provide a good shopping experience for your customers and how you can code that into your application.